﻿'Description:打印类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-10-20
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports System.Text
Public Class uLMSContainerPrintBusiness
    Implements IBusiness.uLMSContainerPrintIBusiness

#Region "打印流水卡"
    Function GetContainerPrintQueryData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strSql As String = <string>
SELECT count(c.Qty),cci.Productname,cci.productdescription, cci.productrevision, to_char(c.Factorystartdate,'yyyy/MM/dd hh24:mi') Factorystartdate, 
cci.bomid, e.Fullname,cdo.Cdoname,decode(mt.materialtypename,'管材',0,c.isselfmfg) isselfmfg,cci.FactoryName,cci.mfgordername,P.PRODUCTID,DECODE(CDO.CDONAME,'Workflow',c.mfgworkflowid,w.workflowid) workflowid,c.togetherflag,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,cci.fromworkcentername,
(NVL(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,(cci.productname||'/'||cci.containerno) ProductBatchhidden
FROM Container c                         
LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid                         
INNER JOIN Product p on p.Productid = c.Productid                                               
INNER JOIN Workflow w ON w.Workflowid = cci.Workflowid                       
INNER JOIN Cdodefinition cdo ON cdo.Cdodefid = w.Cdotypeid                     
LEFT  JOIN Factory fa ON  fa.Factoryid  =cci.originalfactoryid                   
INNER JOIN Employee e ON e.Employeeid = c.startemployeeid                      
LEFT JOIN materialtype mt ON mt.materialtypeid=p.materialtypeid   										              
WHERE c.Parentcontainerid IS NULL AND c.Status = 1 AND c.Qty >= 0 AND c.Containerid != c.Containername AND CCI.WorkflowStepName!='工艺确认'												
                               </string>.Value
        If para.ContainsKey("Fullname") Then
            strSql += String.Format(" AND UPPER(e.Fullname) LIKE UPPER('%{0}%')", para("FullName"))
        End If
        If para.ContainsKey("FactoryName") Then
            strSql += String.Format(" AND (fa.Factoryname='{0}' or cci.Factoryname='{0}')", para("FactoryName"))
        End If

    End Function



    ''' <summary>
    ''' 获取跟踪卡打印信息
    ''' </summary>
    ''' <returns></returns>
    Private Function GetContainerPrintInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSContainerPrintIBusiness.GetContainerPrintInfo
        Dim ds As New DataSet
        Dim strSQL As String = ""
        Dim workflowID As String = ""
        Dim result As New uMESProcessResult

        strSQL = GetContainerMain(QueryCondition)
        Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)

        '批次当前工序的工艺路线
        strSQL = String.Format(" SELECT cci.workflowid FROM containercurrentinfo cci WHERE cci.containerid='{0}'", QueryCondition("ContainerID").ToString.Trim)
        workflowID = DatabaseHelper.Query_ReturnDatTable(strSQL).Rows(0)("workflowID")
        '批次对应零件对应的历史加工工序

        '批次所有报工记录
        strSQL = GetContainerReport(QueryCondition)
        Dim dtReportStep As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)
        If dtReportStep.Rows.Count = 0 Then '如果没有报工记录则查询所有的检测项信息
            strSQL = GetCheckPointInfoByContainerID(QueryCondition)
            dtReportStep = DatabaseHelper.Query_ReturnDatTable(strSQL)
        End If
        dtReportStep.TableName = "dtReportStep"
        ds.Tables.Add(dtReportStep)

        result.DBDataSet = ds
        result.Result = True
        Return result
    End Function


    ''' <summary>
    ''' 获取批次零件信息SQL
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>SUBSTR(C.TOGETHERFLAG, INSTR(C.TOGETHERFLAG, '/') + 1)->SUBSTR(C.TOGETHERFLAG, LENGTH(C.PRODUCTNAME) + 2) tianFW 2018年1月31日</remarks>
    Private Function GetContainerMain(ByVal Query As Dictionary(Of String, String)) As String
        Dim strSql = " SELECT wc.workcentername,pb.productname,c.productid,p.description productdesc,p.charactertype,c.containername," &
                     " SUBSTR(c.mfgbatch,0,LENGTH(c.mfgbatch)-1) mfgbatch, SUBSTR(C.TOGETHERFLAG, LENGTH(pb.PRODUCTNAME) + 2) TOGETHERFLAG," &
                     " P.PRODUCTREVISION, p.materialtrademark,u.attributevalue factorycode,c.qualificationnum,c.containertype,c.militarytype" &
                     " FROM container c " &
                     " LEFT JOIN workcenter wc ON wc.workcenterid=c.workcenterid" &
                     " LEFT JOIN product p ON p.productid=c.productid" &
                     " LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid" &
                     " LEFT JOIN userattribute u ON u.parentid =  c.originalfactoryid AND u.userattributename = '分厂代码'"
        strSql += String.Format(" WHERE c.containerid='{0}'", Query("ContainerID").Trim)
        strSql += "  GROUP BY wc.workcentername,pb.productname,c.productid,p.description,p.charactertype,c.mfgbatch,C.TOGETHERFLAG,P.PRODUCTREVISION,c.containername,p.materialtrademark,u.attributevalue,c.qualificationnum,c.containertype,c.militarytype"
        GetContainerMain = strSql
    End Function

    ''' <summary>
    ''' 获取批次所有报工信息SQL
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetContainerReport(ByVal Query As Dictionary(Of String, String)) As String
        Dim sql As String = <string>
                               SELECT csr.reportdate,csr.containername,csr.specno,csr.dispatchresourcename,csr.reportqty,csr.passqty,csr.lossqty,csr.defectqty,
                                csr.reportemployeename,csr.checkemployeename,scp.checkpointnote,scpr.checkpointresultnote FROM containersteprecord csr
                                LEFT JOIN stepcheckpoint scp ON scp.productid=csr.productid AND scp.workflowstepid=csr.workflowstepid
								LEFT JOIN stepcheckpointresult scpr ON scpr.stepcheckpointid=scp.stepcheckpointid  AND scpr.containerid=csr.containerid                               
                           </string>

        sql += String.Format("WHERE csr.containerid='{0}' AND scp.keycheck=1", Query("ContainerID").Trim)
        sql += " order by csr.specno asc"
        GetContainerReport = sql
    End Function
    ''' <summary>
    ''' 获取批次所有检测项信息
    ''' </summary>
    ''' <param name="Query"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCheckPointInfoByContainerID(ByVal Query As Dictionary(Of String, String)) As String
        Dim strSql As String = " SELECT NULL reportdate,c.containername,s.specno,NULL dispatchresourcename,NULL reportqty,NULL passqty,NULL lossqty,NULL defectqty,NULL reportemployeename,NULL checkemployeename,sc.checkpointnote,NULL checkpointresultnote FROM stepcheckpoint sc " &
                               " LEFT JOIN Workflowstep ws ON ws.workflowstepid=sc.workflowstepid" &
                              "  LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid" &
                               " LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)" &
                               " LEFT JOIN container c ON c.productid=sc.productid"
        strSql += String.Format(" WHERE c.containerid='{0}' AND sc.keycheck=1", Query("ContainerID").Trim)
        strSql += " ORDER BY sc.intime"

        GetCheckPointInfoByContainerID = strSql
    End Function

    ''' <summary>
    ''' 获取批次基本信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetContainerBaseInfo(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerPrintIBusiness.GetContainerBaseInfo
        Dim strSql As String = GetContainerMain(QueryCondition)
        GetContainerBaseInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取检测项信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetCheckItemInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerPrintIBusiness.GetCheckItemInfo

        'SELECT sc.name,sc.checkpointnote,scr.CheckPointResultNote,e.fullname FROM stepcheckpoint sc
        'LEFT JOIN stepcheckpointresult scr ON scr.stepcheckpointid=sc.stepcheckpointid
        'LEFT JOIN employee e ON e.employeeid=scr.checkerid
        'LEFT JOIN product p ON p.productid=sc.productid
        'LEFT JOIN container c ON c.productid=p.productid
        'LEFT JOIN Currentstatus cu ON cu.currentstatusid=c.currentstatusid AND cu.factoryid=scr.factoryid
        'LEFT JOIN workflowstep ws ON ws.workflowstepid=sc.workflowstepid
        Dim strSql As String = <string>SELECT distinct sc.name,sc.checkpointnote,sc.checkpointnotedis,scr.workcheckpointresultnote,scr.workcheckpointresultnotedis,
scr.CheckPointResultNote,scr.checkpointresultnotedis,e.fullname,ws.sequence FROM containercurrentinfo cci
left join container c on c.containerid=cci.containerid
left join workflow w on w.workflowid=decode(cci.isinstore,0,cci.workflowid,c.mfgworkflowid)
left join workflowstep ws on ws.workflowid=w.workflowid
left join stepcheckpoint sc on sc.workflowstepid=ws.workflowstepid
LEFT JOIN stepcheckpointresult scr ON scr.stepcheckpointid=sc.stepcheckpointid and scr.containerid=cci.containerid
LEFT JOIN employee e ON e.employeeid=nvl(scr.checkerid,scr.workerid) </string>
        strSql += String.Format(" WHERE sc.qualitycheck=1 AND c.containerid='{0}'", para("ContainerID"))
        strSql += " order by ws.sequence"
        GetCheckItemInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

#End Region

#Region "首件检验打印单"
    ''' <summary>
    ''' 获取首件检验打印数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetFirstCheckPrintData(ByVal para As Dictionary(Of String, Object)) As DataSet Implements IBusiness.uLMSContainerPrintIBusiness.GetFirstCheckPrintData
        Dim ds As New DataSet
        Dim strSql = <string>
SELECT cci.containername,cci.containerid,cci.productdescription,w.process_no,cci.containerno,csw.firstcheckcontainerno,
cci.productrevision,cci.productname,s.specno,ws.workflowstepid,e1.fullname workername,e2.fullname secondername,e3.fullname checkername,
NVL(ci.createdate,nvl(csw.secondcheckdate,csw.createdate)) checkdate
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN Workflow w ON w.workflowid = decode(cci.isinstore,-1,c.mfgworkflowid,0,cci.workflowid,cci.bsworkflowid) 
LEFT JOIN containerstepworkreport csw ON csw.containerid = cci.containerid AND csw.checktype = 1 AND csw.isuse = 0
LEFT JOIN containercheckinfo ci ON ci.containerstepworkreportid = csw.containerstepworkreportid AND ci.isuse = 0
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
LEFT JOIN employee e1 ON e1.employeeid = csw.workerid
LEFT JOIN employee e2 ON e2.employeeid = csw.secondcheckempid
LEFT JOIN employee e3 ON e3.employeeid = ci.checkerid
WHERE cci.containerid = '{0}' AND cci.containerstatus = 1 AND csw.containerstepworkreportid IS NOT NULL
order by ws.sequence
                     </string>.Value
        strSql = String.Format(strSql, para("ContainerId").ToString)
        Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtMain.TableName = "dtMain"
        ds.Tables.Add(dtMain)
        '查询批次所有首检检测项
        strSql = <string>
SELECT ws.workflowstepid,
sp.checkpointnote,sp.checkpointnotedis,spr.checkpointresultnote,spr.checkpointresultnotedis,spr.workcheckpointresultnote,
spr.workcheckpointresultnotedis,spr.secondcheckpointnotedis
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN Workflow w ON w.workflowid = decode(cci.isinstore,-1,c.mfgworkflowid,cci.workflowid) 
LEFT JOIN containerstepworkreport csw ON csw.containerid = cci.containerid AND csw.checktype = 1 AND csw.isuse = 0
LEFT JOIN containercheckinfo ci ON ci.containerstepworkreportid = csw.containerstepworkreportid AND ci.isuse = 0
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csw.workflowstepid
LEFT JOIN stepcheckpoint sp ON sp.workflowstepid = ws.workflowstepid
LEFT JOIN stepcheckpointresult spr ON spr.stepcheckpointid = sp.stepcheckpointid AND spr.containerid = cci.containerid
WHERE cci.containerid = '{0}' AND cci.containerstatus = 1 AND csw.containerstepworkreportid IS NOT NULL
                     </string>.Value
        strSql = String.Format(strSql, para("ContainerId").ToString)
        Dim dtCheckItem As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        dtCheckItem.TableName = "dtCheckItem"
        ds.Tables.Add(dtCheckItem)
        Return ds
    End Function
#End Region


    ''' <summary>
    ''' 获取已经存在检测项的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Protected Function GetCheckedPonitInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSContainerPrintIBusiness.GetCheckedPonitInfo
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.PLANNEDSTARTDATE,C.PLANNEDCOMPLETIONDATE,C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.ISSELFMFG,(c.productname||'/'||c.containerno) ProductBatch,
            </string>
        Dim sb As New StringBuilder
        Dim sbCondition As String = GetCondition(para)
        sb.AppendLine(strSelect)
        'sb.AppendLine("REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,")
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,'' FaContianerID,NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,''ISKEY,''SPECNAME,''SPECREVISION,''PARENTSPECNO")
        sb.AppendLine(",NVL(C.NONCONFORMINGNUM,0) NONCONFORMINGNUM FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" left JOIN stepcheckpoint sc ON c.productid=sc.productid AND sc.workflowstepid=c.workflowstepid")

        If para("PrintType") = "3" Then '炉号表
            sb.AppendLine("left join container cc on cc.containerid=c.containerid")
        End If

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine(" GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,")
        sb.AppendLine(" C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY, C.PLANNEDSTARTDATE, C.PLANNEDCOMPLETIONDATE,")
        sb.AppendLine(" C.WORKCENTERNAME,C.WORKCENTERID,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.NONCONFORMINGNUM,C.ISSELFMFG,c.containerno")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        'sb.AppendLine("SUBSTR(C.CONTAINERNAME, LENGTH(C.PRODUCTNAME) + 2) TOGETHERFLAGNAME,")
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.WORKREPORTNUM,C.CONTAINERID AS FaContianerID,C.FIRSTCHECKNUM,C.STEPCHECKNUM,C.ISKEY,C.SPECNAME,C.SPECREVISION,C.PARENTSPECNO")
        sb.AppendLine(",NVL(C.NONCONFORMINGNUM,0) NONCONFORMINGNUM FROM CONTAINERCURRENTINFO C ")
        sb.AppendLine(" left JOIN stepcheckpoint sc ON c.productid=sc.productid AND sc.workflowstepid=c.workflowstepid")

        If para("PrintType") = "3" Then '炉号表
            sb.AppendLine("left join container cc on cc.containerid=c.containerid")
        End If

        sb.AppendLine(sbCondition)
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString

        Dim sonSelect As String = "SELECT distinct C.* FROM CONTAINERCURRENTINFO C left JOIN stepcheckpointresult scr ON scr.containerid=c.containerid "
        If para("PrintType") = "3" Then '炉号表
            sonSelect += " left join container cc on cc.containerid=c.containerid"
        End If
        sbCondition = String.Format(sonSelect + " {0} ", sbCondition) '将视图更换为新表 tianfw2017-06-05

        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition)
    End Function


    ''' <summary>
    ''' 创建批次查询数据条件
    ''' Create:YangSJ
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetCondition(ByVal para As Dictionary(Of String, String)) As String
        Dim sbCondition As New StringBuilder
        sbCondition.AppendFormat(" WHERE C.FACTORYID='{0}' ", para("FactoryID"))

        If para.Keys.Contains("ProductName") Then
            sbCondition.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sbCondition.AppendFormat(" AND C.SPECNO LIKE'%{0}%'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND C.CONTAINERNAME LIKE '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("MfgOrderName") Then 'add:Wangjh 2017-8-8
            sbCondition.AppendFormat(" AND C.MfgOrderName LIKE '%{0}%'", para("MfgOrderName"))
        End If
        If para("PrintType") = "0" Then
            sbCondition.AppendLine(" and sc.KeyCheck=1 and C.CONTAINERTYPE<>'7'")
        End If
        If para("PrintType") = "3" Then '炉号表
            sbCondition.AppendLine(" and cc.batchcomments is not null")
        End If
        If para("PrintType") = "4" Then '一次交检合格率--工序不合格特征/原因 add by longb 2023-12-29
            sbCondition.AppendLine(<string>
                  AND EXISTS(
 	                 SELECT 1 FROM CONTAINERCHECKINFO c2 WHERE c2.CONTAINERID =c.CONTAINERID AND c2.WORKFLOWSTEPID =c.workflowstepid
 	                 AND c2.UNQUALIFIEDFEATUREIDS IS NOT NULL AND c2.UNQUALIFIEDFEATUREIDS IS NOT NULL 
                 )
                                   </string>)
        End If
        GetCondition = sbCondition.ToString
    End Function


    ''' <summary>
    ''' 获取批次熔炉号信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetContainerFurnaceNumber(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerPrintIBusiness.GetContainerFurnaceNumber
        Dim strSql As String = <string>
SELECT c.batchcomments,c.containername,u.attributevalue kfbg,u1.attributevalue qcjy,u2.attributevalue flsj
FROM container c 
LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '库房保管'
LEFT JOIN Userattribute u1 ON u1.parentid = c.containerid AND u1.userattributename = '器材检验'
LEFT JOIN Userattribute u2 ON u2.parentid = c.containerid AND u2.userattributename = '发料时间'
WHERE c.containerid='{0}'</string>.Value

        strSql = String.Format(strSql, para("ContainerID"))
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
End Class
